﻿Imports AugmentLib.Database
Imports System.Configuration.ConfigurationManager
Partial Class location
    Inherits System.Web.UI.Page

#Region " Properties "

    Public Property CurrentPage() As Integer
        Get
            'Looks for the current page in ViewState
            Dim objPage As Object = ViewState("_CurrentPage")
            If objPage Is Nothing Then
                Return 0
            Else
                'default to showing the first page
                Return CInt(objPage)
            End If
        End Get
        Set(ByVal value As Integer)
            ViewState("_CurrentPage") = value
        End Set
    End Property

    Public Property PageSize() As Integer
        Get
            'Looks for the current page in ViewState
            Dim objPageSize As Object = ViewState("_PageSize")
            If objPageSize Is Nothing Then
                Return 10
            Else
                'default to showing the first page
                Return CInt(objPageSize)
            End If
        End Get
        Set(ByVal value As Integer)
            ViewState("_PageSize") = value
        End Set
    End Property

#End Region

#Region " Paging and Page Size "

    Protected Sub lnkHead10Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkHead10Item.Click
        PageSize = 10
        LoadLocation()
    End Sub

    Protected Sub lnkHead25Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkHead25Item.Click
        PageSize = 25
       LoadLocation()
    End Sub

    Protected Sub lnkHead100Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkHead100Item.Click
        PageSize = 100
      LoadLocation()
    End Sub

    Protected Sub lnkHead150Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkHead150Item.Click
        PageSize = 150
        LoadLocation()
    End Sub

    Protected Sub lnkFoot10Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkFoot10Item.Click
        PageSize = 10
        LoadLocation()
    End Sub

    Protected Sub lnkFoot25Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkFoot25Item.Click
        PageSize = 25
        LoadLocation()
    End Sub

    Protected Sub lnkFoot100Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkFoot100Item.Click
        PageSize = 100
        LoadLocation()
    End Sub

    Protected Sub lnkFoot150Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkFoot150Item.Click
        PageSize = 150
       LoadLocation()
    End Sub

    Protected Sub btnMoveFirst_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnMoveFirst.Click
        CurrentPage = 0
        txtPageNo.Text = CurrentPage + 1
        LoadLocation()
    End Sub

    Protected Sub btnPrevious_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
        If CurrentPage > 0 Then
            CurrentPage = CurrentPage - 1
            txtPageNo.Text = CurrentPage + 1
            LoadLocation()
        End If
    End Sub

    Protected Sub btnGo_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGo.Click
        If CInt(txtPageNo.Text < 1) Then
            txtPageNo.Text = 1
        End If

        If CInt(txtPageNo.Text > CInt(lblMaxPageCount.Text)) Then
            txtPageNo.Text = CInt(lblMaxPageCount.Text) + 1
        End If

        CurrentPage = CInt(txtPageNo.Text) - 1
       LoadLocation()

    End Sub

    Protected Sub btnNext_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnNext.Click
        If CurrentPage < CInt(lblMaxPageCount.Text) Then
            CurrentPage = CurrentPage + 1
            txtPageNo.Text = CurrentPage + 1
            LoadLocation()
        End If
    End Sub

    Protected Sub btnMoveLast_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnMoveLast.Click
        CurrentPage = CInt(lblMaxPageCount.Text)
        txtPageNo.Text = CurrentPage + 1
        LoadLocation()
    End Sub

#End Region

    Private Sub LoadLocation()

        Dim CountryFilter As String = String.Empty
        Dim StateFilter As String = String.Empty
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        Dim TempDT As New Data.DataTable

        objDB.OpenConnection()

        'to add in filtering for country, state
        'edited by minginn @ 28/6/2011 - to load the listing according to search criteria after edit but not after save
        'If strCountry <> "" Then
        '    CountryFilter = " AND s.CountryId = '" & strCountry & "'"
        'End If

        'If strState <> "" Then
        '    StateFilter = " AND s.StateId = '" & strState & "'"
        'End If
        If ddlCountryFilter.SelectedValue <> "" Then
            CountryFilter = " AND s.CountryId = '" & ddlCountryFilter.SelectedValue & "'"
        End If

        If ddlStateFilter.SelectedValue <> "" Then
            StateFilter = " AND s.StateId = '" & ddlStateFilter.SelectedValue & "'"
        End If

        If txtSearch.Text = "" Then
            objDB.dbVariable.SQLString = "SELECT LocationId,Location,l.StateId,s.CountryId,State,Country FROM AW_State s " & _
                                        "INNER JOIN AW_Country c ON s.CountryId = c.CountryId " & _
                                        "INNER JOIN AW_Location l ON l.StateId = s.StateId " & _
                                        "WHERE l.StatusDelete=0" & CountryFilter & StateFilter & lblHidSortType.Text
            objDB.FillData(TempDT, "AW_Location", Database.SQLCommandType.NormalString)
        Else
            objDB.dbVariable.SQLString = "SELECT LocationId,Location,LocationId,l.StateId,s.CountryId,State,Country FROM AW_State s " & _
                                        "INNER JOIN AW_Country c ON s.CountryId = c.CountryId " & _
                                        "INNER JOIN AW_Location l ON l.StateId = s.StateId " & _
                                        "WHERE l.StatusDelete=0 AND Location LIKE @Location" & CountryFilter & StateFilter & lblHidSortType.Text
            objDB.AddParameter("Location", Data.SqlDbType.NVarChar, "%" & txtSearch.Text & "%")
            objDB.FillData(TempDT, "AW_Location", Database.SQLCommandType.NormalString)
        End If
        objDB.CloseConnection()

        'Creating a Paged Datasouce
        Dim objPaged As New PagedDataSource
        objPaged.DataSource = TempDT.DefaultView
        objPaged.AllowPaging = True
        objPaged.PageSize = PageSize
        objPaged.CurrentPageIndex = CurrentPage

        lblHeadNoOfItem.Text = TempDT.Rows.Count
        lblFootNoOfItem.Text = TempDT.Rows.Count

        repLocation.DataSource = objPaged
        repLocation.DataBind()

        lblMaxPageCount.Text = objPaged.PageCount - 1
        lblTotalPage.Text = " of " & objPaged.PageCount


    End Sub

    Protected Sub imgNewLocation_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles imgNewLocation.Click
        GetddlCountry()
        GetddlState(ddlCountry.SelectedValue)
        txtLocation.Text = String.Empty
        ClearAllField()
        mpeEntry.Show()
    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Session("StaffId") Is Nothing Then
            Response.Redirect("Default.aspx")
        ElseIf Session.Item("StaffType").ToString = "2" Then
            Response.Redirect("Default.aspx")
        Else
            lblStaffUID.Text = Session("StaffId").ToString
        End If

        If Not Page.IsPostBack Then
            GetddlCountryFilter()
            GetddlStateFilter(ddlCountryFilter.SelectedValue)
            LoadLocation()
            'Added by : Daniel Lee
            'Date : 22-Apr-2011
            'Purpose : To auto update each other when typing the search criteria
            txtSearch.Attributes.Add("onKeyUp", "SearchReplace('" & txtSearch.ClientID & "','" & txtFilterSearch.ClientID & "')")
            txtSearch.Attributes.Add("onPaste", "SearchReplace('" & txtSearch.ClientID & "','" & txtFilterSearch.ClientID & "')")
            txtFilterSearch.Attributes.Add("onKeyUp", "SearchReplace('" & txtFilterSearch.ClientID & "','" & txtSearch.ClientID & "')")
            txtFilterSearch.Attributes.Add("onPaste", "SearchReplace('" & txtFilterSearch.ClientID & "','" & txtSearch.ClientID & "')")
        End If
    End Sub

    Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        ddlCountryFilter.SelectedIndex = 0
        ddlStateFilter.SelectedIndex = 0
        'Removed By :  Daniel Lee
        'Date : 22-April-2011
        'Purpose : Moved this code into javascript
        'txtFilterSearch.Text = txtSearch.Text.Trim
        LoadLocation()
    End Sub

    Protected Sub repLocation_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.RepeaterCommandEventArgs) Handles repLocation.ItemCommand
        If e.CommandName.ToUpper = "EDITITEM" Then
            GetddlCountry()
            ddlCountry.SelectedValue = CType(repLocation.Items(e.CommandArgument).FindControl("lblRepCountryUID"), Label).Text
            GetddlState(ddlCountry.SelectedValue)
            ddlState.SelectedValue = CType(repLocation.Items(e.CommandArgument).FindControl("lblRepStateUID"), Label).Text
            txtLocation.Text = CType(repLocation.Items(e.CommandArgument).FindControl("lblRepLocation"), Label).Text
            lblLocationUID.Text = CType(repLocation.Items(e.CommandArgument).FindControl("lblRepLocationUID"), Label).Text
            mpeEntry.Show()
        Else
            'Begin Delete
            Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))

            objDB.OpenConnection()
            objDB.dbVariable.SQLString = "UPDATE AW_Location SET StatusDelete=1 WHERE LocationId=@LocationId"
            objDB.AddParameter("LocationId", Data.SqlDbType.UniqueIdentifier, New Guid(CType(repLocation.Items(e.CommandArgument).FindControl("lblRepLocationUID"), Label).Text))
            objDB.ExecuteQuery()
            objDB.CloseConnection()
            lblMsgSuccessful.Text = "Location Deleted"
            lblMsgSuccessful.Visible = True
           LoadLocation()
        End If
    End Sub

    Private Sub ClearAllField()
        txtLocation.Text = String.Empty
        ddlCountry.SelectedIndex = 0
        ddlState.SelectedIndex = 0
        lblLocationUID.Text = String.Empty
        divError.Visible = False
        divSuccessful.Visible = False
    End Sub

    Private Sub ClearFilterField()
        txtFilterSearch.Text = String.Empty
        txtSearch.Text = String.Empty
        ddlCountryFilter.SelectedIndex = 0
        ddlStateFilter.SelectedIndex = 0
    End Sub

    Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click

        If lblLocationUID.Text = "" Then
            'New
            Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))

            objDB.OpenConnection()
            Dim ExistLocation As String
            ExistLocation = LocationExist(objDB)
            If ExistLocation = "N" Then
                ClearAllField()
                ClearFilterField()
                divError.Visible = True
                lblMsgError.Text = "Location Already Exist"
                LoadLocation()
            ElseIf ExistLocation <> "" Then
                objDB.dbVariable.SQLString = "UPDATE AW_Location SET StatusDelete=@StatusDelete WHERE LocationId=@LocationId"
                objDB.AddParameter("LocationId", Data.SqlDbType.UniqueIdentifier, New Guid(ExistLocation))
                objDB.AddParameter("StatusDelete", Data.SqlDbType.TinyInt, 0)
                objDB.ExecuteQuery()
                objDB.CloseConnection()
                ClearAllField()
                ClearFilterField()
                divSuccessful.Visible = True
                lblMsgSuccessful.Text = "Location Successfully Updated"
                LoadLocation()
            Else
                objDB.dbVariable.SQLString = "INSERT INTO AW_Location (LocationId,StateId,Location,CreateDate,StatusDelete) VALUES (@LocationId,@StateId,@Location,@CreateDate,@StatusDelete)"
                objDB.AddParameter("LocationId", Data.SqlDbType.UniqueIdentifier, Guid.NewGuid)
                objDB.AddParameter("StateId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlState.SelectedValue))
                objDB.AddParameter("Location", Data.SqlDbType.NVarChar, txtLocation.Text.Trim.ToUpper)
                objDB.AddParameter("CreateDate", Data.SqlDbType.DateTime, Now)
                objDB.AddParameter("StatusDelete", Data.SqlDbType.TinyInt, 0)
                objDB.ExecuteQuery()
                ClearAllField()
                ClearFilterField()
                divSuccessful.Visible = True
                lblMsgSuccessful.Text = "Location Successfully Inserted"
                LoadLocation()
            End If
            objDB.CloseConnection()
        Else
            'Edit
            Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))

            objDB.OpenConnection()
            objDB.dbVariable.SQLString = "UPDATE AW_Location SET Location=@Location, StateId=@StateId WHERE LocationId=@LocationId"
            objDB.AddParameter("LocationId", Data.SqlDbType.UniqueIdentifier, New Guid(lblLocationUID.Text))
            objDB.AddParameter("StateId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlState.SelectedValue))
            objDB.AddParameter("Location", Data.SqlDbType.NVarChar, txtLocation.Text.Trim.ToUpper)
            objDB.ExecuteQuery()
            objDB.CloseConnection()
            ClearAllField()
            divSuccessful.Visible = True
            lblMsgSuccessful.Text = "Location Successfully Updated"
            LoadLocation()
        End If
    End Sub

    Private Function LocationExist(ByRef objDB As Database) As String
        Dim Tempdt As New Data.DataTable
        Dim strLocation As String = String.Empty

        objDB.dbVariable.SQLString = "SELECT Location, StatusDelete, LocationId FROM AW_Location WHERE StateId=@StateId AND Location=@Location"
        objDB.AddParameter("StateId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlState.SelectedValue))
        objDB.AddParameter("Location", Data.SqlDbType.NVarChar, txtLocation.Text.Trim.ToUpper)
        objDB.FillData(Tempdt, "AW_Location", Database.SQLCommandType.NormalString)
        objDB.ClearParameter()
        If Tempdt.Rows.Count > 0 Then
            For Each row As Data.DataRow In Tempdt.Rows
                If row.Item("StatusDelete").ToString = "0" Then
                    strLocation = "N"
                Else
                    strLocation = row.Item("LocationId").ToString
                End If
            Next
        Else
            strLocation = ""
        End If
        Return strLocation
    End Function

    Private Sub GetddlCountry()
        Dim TempDT As New Data.DataTable
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        objDB.dbVariable.SQLString = "SELECT CountryId,Country FROM AW_Country WHERE StatusDelete = 0 ORDER BY Country"
        objDB.FillData(TempDT, "Country", Database.SQLCommandType.NormalString)

        'Dim dtrow As Data.DataRow
        'dtrow = TempDT.NewRow()
        'dtrow.Item(0) = "00000000-0000-0000-0000-000000000000"
        'dtrow.Item(1) = "-SELECT-"
        'TempDT.Rows.InsertAt(dtrow, 0)

        With ddlCountry
            .DataSource = TempDT
            .DataTextField = "Country"
            .DataValueField = "CountryId"
            .DataBind()
            .Items.Insert(0, "-SELECT-")
            .Items(0).Value = ""
        End With

        objDB.CloseConnection()
    End Sub

    Private Sub GetddlState(ByVal strCountry As String)
        Dim TempDT As New Data.DataTable
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        If strCountry = "" Then
            strCountry = Guid.Empty.ToString
        End If

        objDB.dbVariable.SQLString = "SELECT StateId,State FROM AW_State WHERE CountryId = @CountryId AND StatusDelete = 0 ORDER BY State"
        objDB.AddParameter("CountryId", Data.SqlDbType.UniqueIdentifier, New Guid(strCountry))
        objDB.FillData(TempDT, "State", Database.SQLCommandType.NormalString)

        'Dim dtrow As Data.DataRow
        'dtrow = TempDT.NewRow()
        'dtrow.Item(0) = "00000000-0000-0000-0000-000000000000"
        'dtrow.Item(1) = "-SELECT-"
        'TempDT.Rows.InsertAt(dtrow, 0)

        With ddlState
            .DataSource = TempDT
            .DataTextField = "State"
            .DataValueField = "StateId"
            .DataBind()
            .Items.Insert(0, "-SELECT-")
            .Items(0).Value = ""
        End With

        objDB.CloseConnection()

    End Sub

    Private Sub GetddlCountryFilter()
        Dim TempDT As New Data.DataTable
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        objDB.dbVariable.SQLString = "SELECT CountryId,Country FROM AW_Country WHERE StatusDelete = 0 ORDER BY Country"
        objDB.FillData(TempDT, "Country", Database.SQLCommandType.NormalString)

        'Dim dtrow As Data.DataRow
        'dtrow = TempDT.NewRow()
        'dtrow.Item(0) = "00000000-0000-0000-0000-000000000000"
        'dtrow.Item(1) = "ANY"
        'TempDT.Rows.InsertAt(dtrow, 0)

        With ddlCountryFilter
            .DataSource = TempDT
            .DataTextField = "Country"
            .DataValueField = "CountryId"
            .DataBind()
            .Items.Insert(0, "ANY")
            .Items(0).Value = ""
        End With

        objDB.CloseConnection()

    End Sub


    Private Sub GetddlStateFilter(ByVal strCountry As String)
        Dim TempDT As New Data.DataTable
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        If strCountry = "" Then
            strCountry = Guid.Empty.ToString
        End If

        objDB.dbVariable.SQLString = "SELECT StateId,State FROM AW_State WHERE CountryId = @CountryId AND StatusDelete = 0 ORDER BY State"
        objDB.AddParameter("CountryId", Data.SqlDbType.UniqueIdentifier, New Guid(strCountry))
        objDB.FillData(TempDT, "State", Database.SQLCommandType.NormalString)

        'Dim dtrow As Data.DataRow
        'dtrow = TempDT.NewRow()
        'dtrow.Item(0) = "00000000-0000-0000-0000-000000000000"
        'dtrow.Item(1) = "ANY"
        'TempDT.Rows.InsertAt(dtrow, 0)      

        With ddlStateFilter
            .DataSource = TempDT
            .DataTextField = "State"
            .DataValueField = "StateId"
            .DataBind()
            .Items.Insert(0, "ANY")
            .Items(0).Value = ""
        End With

        objDB.CloseConnection()

    End Sub

    Protected Sub btnSearchFilter_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearchFilter.Click
        txtSearch.Text = txtFilterSearch.Text.Trim
        LoadLocation()
    End Sub

    Protected Sub btnResetSearchFilter_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnResetSearchFilter.Click
        txtFilterSearch.Text = String.Empty
        txtSearch.Text = String.Empty
        ddlCountryFilter.SelectedIndex = 0
        ddlStateFilter.SelectedIndex = 0
    End Sub

    Protected Sub ddlCountryFilter_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlCountryFilter.SelectedIndexChanged
        GetddlStateFilter(ddlCountryFilter.SelectedValue)
        LoadLocation()
    End Sub

    Protected Sub ddlStateFilter_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlStateFilter.SelectedIndexChanged
        LoadLocation()
    End Sub

    Protected Sub ddlCountry_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlCountry.SelectedIndexChanged
        GetddlState(ddlCountry.SelectedValue)
        mpeEntry.Show()
    End Sub

    Private Function SortType(ByVal strSortType As String) As String
        Dim strSort As String
        If Right(strSortType, 1) = "▼" Then
            strSort = "ASC"
        Else
            strSort = "DESC"
        End If
        Return strSort
    End Function

    Protected Sub lnkLocation_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkLocation.Click
        If lnkLocation.Text = "Location▼" Then
            lnkLocation.Text = "Location▲"
        Else
            lnkLocation.Text = "Location▼"
        End If

        lblHidSortType.Text = " ORDER BY Location " & SortType(lnkLocation.Text) & ", State " & SortType(lnkState.Text) & ", Country " & SortType(lnkCountry.Text)
        LoadLocation()
    End Sub

    Protected Sub lnkState_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkState.Click
        If lnkState.Text = "State▼" Then
            lnkState.Text = "State▲"
        Else
            lnkState.Text = "State▼"
        End If

        lblHidSortType.Text = " ORDER BY State " & SortType(lnkState.Text) & ", Location " & SortType(lnkLocation.Text) & ", Country " & SortType(lnkCountry.Text)
        LoadLocation()
    End Sub

    Protected Sub lnkCountry_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkCountry.Click
        If lnkCountry.Text = "Country▼" Then
            lnkCountry.Text = "Country▲"
        Else
            lnkCountry.Text = "Country▼"
        End If

        lblHidSortType.Text = " ORDER BY Country " & SortType(lnkCountry.Text) & ", Location " & SortType(lnkLocation.Text) & ", State " & SortType(lnkState.Text)
        LoadLocation()
    End Sub

    Protected Sub repLocation_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles repLocation.ItemDataBound
        If e.Item.ItemType = ListItemType.AlternatingItem Or e.Item.ItemType = ListItemType.Item Then
            CType(e.Item.FindControl("cbeDelete"), Object).confirmText = "Are you sure you want to delete " & CType(e.Item.FindControl("lblRepLocation"), Label).Text
        End If
    End Sub
End Class
